﻿Imports System.Data.OleDb
Imports DTO

Public Class TheLoaiDAO

    Public Function layTheLoaiTheoMa(ByVal tl As TheLoaiDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select * from TheLoai where idTheLoai = ? and DaXoa=0 "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        cmd.Parameters("@idTheLoai").Value = tl.LayIdTheLoai
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()

        If kq.Rows.Count > 0 Then
            Dim a1 As Integer = 1
        End If
        Return kq
    End Function

    Public Function layTheLoai() As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select * from TheLoai where DaXoa=0"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Sub ThemTheLoai(ByVal sach As TheLoaiDTO)
        Dim cn As OleDbConnection
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "Insert into TheLoai (TenTheLoai, DaXoa) values (?, ?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@TenTheLoai", OleDbType.WChar)
        cmd.Parameters.Add("@DaXoa", OleDbType.Integer)

        cmd.Parameters("@TenTheLoai").Value = sach.LayTenTheLoai
        cmd.Parameters("@DaXoa").Value = 0

        cmd.ExecuteNonQuery()

        'B5: Dong ket noi CSDL
        cn.Close()


    End Sub


    Public Function LayTheLoai_idSach(ByVal idsach As SachDTO) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = DatabaseProvider.ConnectionData()
        Dim sql As String = " select TheLoai.idTheLoai, TheLoai.TenTheLoai from Sach, TheLoai where sach.idTheLoai = TheLoai.idTheLoai and  DaXoa = 0 and sach.idSach = '" & idsach.LayIdSach & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(dt)
        cn.Close()
        Return dt
    End Function


    Public Sub XoaTheLoai(ByVal idTheLoai As TheLoaiDTO)
        Dim cn As OleDbConnection
        cn = DatabaseProvider.ConnectionData


        Dim sql As String = "update TheLoai set  DaXoa = 1 where idTheLoai = ? "   '  ' ' ' ''" & idTheLoai.LayIdTheLoai & "'
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idTheLoai", OleDbType.Integer)
        cmd.Parameters("@idTheLoai").Value = idTheLoai.LayIdTheLoai


        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub
End Class
